import json,pymysql
import re
from datetime import datetime
from pampy import match, HEAD, TAIL, _
f = open('medical_server_%s.xlsx' % datetime.now().__format__('%m-%d'),'r',encoding='utf-8')
data = json.load(f)

def conn_mysql(sql,value):
    dbparam = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': '1024',
        'database': 'alerts',
        'charset': 'utf8'
    }
    conn = pymysql.connect(**dbparam)
    cursor = conn.cursor()

    try:
        cursor.execute(sql,value)
        conn.commit()
    except Exception as e:
        print('入库失败', e)
        conn.rollback()
    finally:
        cursor.close()
        conn.close()

url = ''

for info in data:
    print(111,info)
    sql = "insert into weekly_check(project,ip,cpu,mem,disk,network,vda1,vdb1,service,url) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    disk_status = info['disk_status']
    url_list = []
    service = [item for item in info if item not in ['ip', 'cpu_usage', 'mem_usage', 'disk_status', 'network','url']]

    #医疗服务器的服务一般只有一个，名字统一替换为'service'
    if service:
        pass
    else:
        info['service'] = ''

    # 网络连接
    if info['network'] == 'Network: OK':
        info['network'] = '正常'
    else:
        info['network'] = ''
    # 磁盘字符串长度大于60说明有2块磁盘
    if len(disk_status) > 60:
        #取出两块磁盘vda1,vdb1的使用率
        re_regular = re.compile('diskname: (/\w+/\w+).*USAGE: (.*)% diskname: (/\w+/\w+).*USAGE: (.*)%')
        m = match(disk_status, re_regular, lambda a,b,c,d: b+','+d)
        vda1,vdb1 = m.split(',')

        disk_status = info['disk_status']
        #去掉第一个USAGE
        disk_status = re.sub('USAGE: .*% ', '', disk_status)
        # 去掉第二个USAGE
        disk_status = re.sub(' USAGE: .*%', '', disk_status)
        #去掉第一个diskname，第二个diskname替换为换行符<br>
        disk_status = disk_status.replace('diskname: ', '', 1).replace(' diskname: ', '<br><br>')
        print(777, info)
        conn_mysql(sql, ('医疗',info['ip'], info['cpu_usage'], info['mem_usage'].strip('%'),\
                         disk_status,info['network'],vda1,vdb1,info['service'],''))
    else:
        re_regular = re.compile('diskname: (/\w+/\w+).*USAGE: (.*)%')
        m = match(disk_status, re_regular, lambda a,b: b)
        print('m',info['disk_status'])
        # 去掉USAGE
        disk_status = re.sub(' USAGE: .*%', '', disk_status)
        # 去掉diskname
        disk_status = disk_status.replace('diskname: ', '')

        conn_mysql(sql, ('医疗',info['ip'], info['cpu_usage'], info['mem_usage'].strip('%'),\
                         disk_status,info['network'],m,'无',info['service'],''.join(url_list)))
